﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Web;
using System.Xml;
using Daneas.Utility.Data;
using System.Configuration;

namespace Daneas.Utility.Universals
{
    /// <summary>
    /// 代码生成器，有些方法需分拆一下
    /// <remarks>BaseCodePath项目根目录，ModelNameSpace，DALNameSpace命名空间你懂得</remarks>
    /// </summary>
    public class CodeGenerator
    {
        #region 生成路径配置
        private static string rootPath = ConfigurationManager.AppSettings["BaseCodePath"].ToString();
        private static string ModelNameSpace = ConfigurationManager.AppSettings["ModelNameSpace"].ToString();
        private static string DALNameSpace = ConfigurationManager.AppSettings["DALNameSpace"].ToString();

        private static string projPath = rootPath + "BaseCode/BaseCode.csproj";
        private static string modelPath = rootPath + "BaseCode/Model";
        private static string baseDalPath = rootPath + "BaseCode/BaseDAL";
        #endregion

        #region 获取表名字
        private static string tablesSql = "SELECT name,object_id FROM sys.tables WHERE type='U' ORDER BY name";
        #endregion

        #region 获取所有表中字段类型的sql：
        private static string colunmsSql = @"SELECT 
                                    (
	                                    CASE WHEN a.colorder=1 THEN d.name ELSE '' END) 表名,
	                                    a.colorder 字段序号,
	                                    a.name 字段名,
	                                    (CASE WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN '1'ELSE '0' END) 标识,
                                    (CASE WHEN (SELECT count(*) 
                                    FROM sysobjects 
                                    WHERE (name in (SELECT name
                                    FROM sysindexes
                                    WHERE (id = a.id) AND (indid in 
	                                    (
		                                    SELECT indid
		                                    FROM sysindexkeys
		                                    WHERE (id = a.id) AND (colid in 
		                                    (
		                                    SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)
		                                    )
	                                    )
                                    )
                                    )
                                    )
                                    ) AND (xtype = 'PK') 
                                    ) > 0 THEN '1' ELSE '0' END) 主键,
                                    b.name 类型,
                                    a.length 占用字节数,
                                    COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 长度,
                                    ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS 小数位数,
                                    (CASE WHEN a.ISNULLable=1 THEN '1'ELSE '0' END) 允许空,
                                    ISNULL(e.text,'') 默认值,
                                    ISNULL(g.[value],'') AS 字段说明   

                                    FROM  syscolumns a 
                                    LEFT JOIN systypes b on a.xtype=b.xusertype
                                    INNER JOIN sysobjects d on a.id=d.id  and  d.xtype='U' and d.name<>'dtproperties'
                                    LEFT JOIN syscomments e on a.cdefault=e.id
                                    LEFT JOIN sys.extENDed_properties g on a.id=g.major_id AND a.colid = g.minor_id  
                                    where d.name = ('{0}')---查询具体的表，注释掉后就是查询整个数据库了
                                    ORDER BY a.id,a.colorder";

        #endregion

        #region 代码生成

        public bool CreateBaseCode()
        {
            try
            {
                DataTable dt = DBHelper.ExecutQuery(tablesSql).Tables[0];

                try
                {
                    CreateModels(dt);
                }
                catch
                { }

                try
                {
                    CreateBaseDAL(dt);
                }
                catch
                { }

                return true;
            }
            catch
            {
                return false;
            }
        }

        public bool CreateModels()
        {
            DataTable dt = DBHelper.ExecutQuery(tablesSql).Tables[0];

            CreateModels(dt);

            return true;
        }

        public bool CreateBaseDAL()
        {
            DataTable dt = DBHelper.ExecutQuery(tablesSql).Tables[0];

            CreateBaseDAL(dt);

            return true;
        }

        /// <summary>
        /// 生成Model
        /// </summary>
        /// <param name="dt">对象表</param>
        /// <remarks>//0.表名；1.字段序号；2.字段名；3.标示；4.主键；5.类型；6.占用字节长度；7.小数位数；8.允许空；9.默认值；10.字段说明</remarks>
        /// <returns></returns>
        private bool CreateModels(DataTable dt)
        {
            if (dt.Rows.Count < 1)
            {
                return true;
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string tableName = dt.Rows[i]["name"].ToString();
                DataTable dc = DBHelper.ExecutQuery(string.Format(colunmsSql, tableName)).Tables[0];
                if (dc.Rows.Count < 1)
                {
                    continue;
                }
                //0.表名；1.字段序号；2.字段名；3.标示；4.主键；5.类型；
                //6.占用字节长度；7.小数位数；8.允许空；9.默认值；10.字段说明


                //重写商城的时候使用
                //tableName = tableName.Replace("33hao_","");

                StringBuilder model = new StringBuilder();

                model.Append("using System;\r\n");
                model.Append("using Daneas.Utility.Data;\r\n");
                model.Append("\r\n");
                model.Append("namespace " + ModelNameSpace + " \r\n");
                model.Append("{\r\n");
                model.Append("/// <summary>\r\n");
                model.AppendFormat("/// {0}:实体类(属性说明自动提取数据库字段的描述信息)\r\n", tableName);
                model.Append("/// </summary>\r\n");
                model.Append("[Serializable]\r\n");
                model.AppendFormat("public partial class {0}\r\n", tableName);
                model.Append("{\r\n");

                //构造字段
                for (int j = 0; j < dc.Rows.Count; j++)
                {
                    model.Append("///<summary>\r\n");
                    model.AppendFormat("///字段描述：{0}\r\n", dc.Rows[j][11].ToString());
                    model.Append("///</summary>\r\n");

                    SqlDbType sqlColumType = Convers.SqlTypeString2SqlType(dc.Rows[j][5].ToString());
                    string type = dc.Rows[j][2].ToString();

                    model.Append("public " + Convers.SqlType2CsharpType(sqlColumType).ToString() + " " + dc.Rows[j][2].ToString() + " { get; set; }\r\n\r\n");
                    model.Append("");
                    model.Append("");
                    model.Append("");
                    model.Append("");
                }

                model.Append("}\r\n");
                model.Append("}\r\n");

                WriteToModels(tableName, model.ToString());
            }

            return true;
        }

        /// <summary>
        /// 生成DAL的基础方法
        /// </summary>
        /// <param name="dt">对象表</param>
        /// <remarks>//0.表名；1.字段序号；2.字段名；3.标示；4.主键；5.类型；6.占用字节长度；7.小数位数；8.允许空；9.默认值；10.字段说明</remarks>
        /// <returns></returns>
        private bool CreateBaseDAL(DataTable dt)
        {
            if (dt.Rows.Count < 1)
            {
                return true;
            }

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string tableName = dt.Rows[i]["name"].ToString();
                DataTable dc = DBHelper.ExecutQuery(string.Format(colunmsSql, tableName)).Tables[0];
                if (dc.Rows.Count < 1)
                {
                    continue;
                }
                //0.表名；1.字段序号；2.字段名；3.标示；4.主键；5.类型；
                //6.占用字节长度；7.小数位数；8.允许空；9.默认值；10.字段说明                                           

                StringBuilder model = new StringBuilder();
                model.Append("using System;\r\n");
                model.Append("using System.Data;\r\n");
                model.Append("using System.Text;\r\n");
                model.Append("using System.Data.SqlClient;\r\n");
                model.Append("using System.Collections.Generic;\r\n");
                model.Append("using Daneas.Utility.Data;\r\n");
                model.Append("using Daneas.Utility.Universals;\r\n");
                model.Append("using " + ModelNameSpace + ";\r\n");
                model.Append("\r\n");
                model.Append("namespace " + DALNameSpace + " \r\n");
                model.Append("{\r\n");
                model.Append("/// <summary>\r\n");
                model.AppendFormat("/// {0}:基础数据操作类(属性说明自动提取数据库字段的描述信息)\r\n", tableName);
                model.Append("/// </summary>\r\n");
                model.AppendFormat("public partial class {0}BaseDAL\r\n", tableName);
                model.Append("{\r\n");

                model.Append(IsExist(tableName));
                model.Append("\r\n");
                model.Append("\r\n");
                model.Append(GetById(dc, tableName));
                model.Append("\r\n");
                model.Append("\r\n");
                model.Append(Create(dc, tableName));
                model.Append("\r\n");
                model.Append("\r\n");
                model.Append(Update(dc, tableName));
                model.Append("\r\n");
                model.Append("\r\n");
                model.Append(Delete(dc, tableName));
                model.Append("\r\n");
                model.Append("\r\n");
                model.Append(GetList(tableName));
                model.Append("\r\n");
                model.Append("\r\n");
                model.Append(GetList(dc, tableName));
                model.Append("\r\n");
                model.Append("\r\n");
                model.Append(GetQuery(tableName));
                model.Append("\r\n");
                model.Append("\r\n");
                model.Append(GetAllModels(dc, tableName));
                model.Append("\r\n");
                model.Append("\r\n");
                model.Append(GetTopBy(tableName));
                model.Append("\r\n");
                model.Append("\r\n");
                model.Append(GetSelectQuery(tableName));
                model.Append("}\r\n");
                model.Append("}\r\n");

                WriteToDals(tableName, model.ToString());

            }

            return true;
        }

        #endregion

        #region 构造baseDAL

        /// <summary>
        /// 一般和事务新增
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static string Create(DataTable dt, string tableName)
        {
            StringBuilder sb = new StringBuilder();

            SqlDbType sqldbtype = Convers.SqlTypeString2SqlType(dt.Rows[0][5].ToString());

            string returnStr = "";
            if (sqldbtype == SqlDbType.BigInt)
            {
                returnStr = "Convert.ToInt64(obj)";
            }
            else if (sqldbtype == SqlDbType.Int)
            {
                returnStr = "Convert.ToInt32(obj)";
            }
            else if (sqldbtype == SqlDbType.NVarChar)
            {
                returnStr = "Convert.ToString(obj)";
            }

            #region 返回id的方法内容
            StringBuilder innerR = new StringBuilder();
            innerR.Append("     StringBuilder sql = new StringBuilder();\r\n");
            innerR.Append("     sql.Append(\"INSERT INTO " + tableName + " ( \");\r\n");

            int j = 0;
            if (dt.Rows[0][3].ToString() == "0")
            {
                j = 0;
            }
            else
            {
                j = 1;
            }

            string items = "";
            for (int i = j; i < dt.Rows.Count; i++)
            {
                items += dt.Rows[i][2].ToString() + ",";
            }
            innerR.Append("     sql.Append(\"" + items.TrimEnd(',') + "\");\r\n");
            innerR.Append("     sql.Append(\") VALUES( \");\r\n");

            items = "";
            for (int i = j; i < dt.Rows.Count; i++)
            {
                items += "@" + dt.Rows[i][2].ToString() + ",";
            }
            innerR.Append("     sql.Append(\"" + items.TrimEnd(',') + "\");\r\n");
            innerR.Append("     sql.Append(\") \");\r\n");

            innerR.Append("     sql.Append(\";select @@IDENTITY  \");\r\n");
            innerR.Append("\r\n");

            innerR.Append("     List<SqlParameter> paramList = new List<SqlParameter>();\r\n");
            for (int i = j; i < dt.Rows.Count; i++)
            {
                Type type = Convers.SqlType2CsharpType(Convers.SqlTypeString2SqlType(dt.Rows[i][5].ToString()));
                if (type == typeof(DateTime))
                {
                    innerR.Append("     paramList.Add(DBHelper.InitSqlParam(\"@" + dt.Rows[i][2].ToString() + "\",model." + dt.Rows[i][2].ToString() + "==DateTime.MinValue?new DateTime(1900,1,1):model." + dt.Rows[i][2].ToString() + "));\r\n");
                }
                else if (type == typeof(String))
                {
                    innerR.Append("     paramList.Add(DBHelper.InitSqlParam(\"@" + dt.Rows[i][2].ToString() + "\",model." + dt.Rows[i][2].ToString() + "==null?\"\":model." + dt.Rows[i][2].ToString() + "));\r\n");
                }
                else if (type == typeof(Int16) || type == typeof(Int32) || type == typeof(Int64) || type == typeof(Double) || type == typeof(float))
                {
                    innerR.Append("     paramList.Add(DBHelper.InitSqlParam(\"@" + dt.Rows[i][2].ToString() + "\",model." + dt.Rows[i][2].ToString() + "));\r\n");
                }
                else
                {
                    innerR.Append("     paramList.Add(DBHelper.InitSqlParam(\"@" + dt.Rows[i][2].ToString() + "\",model." + dt.Rows[i][2].ToString() + "));\r\n");
                }
            }

            innerR.Append("\r\n");
            #endregion

            #region 生成的方法

            sb.Append("/// <summary>\r\n");
            sb.Append("/// 新增实例(返回ID)\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <param name=\"model\"></param>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static " + Convers.SqlType2CsharpType(sqldbtype) + " Create(" + tableName + " model)\r\n");
            sb.Append("{ \r\n");
            sb.Append("    " + innerR.ToString());
            sb.Append("\r\n");
            sb.Append("    object obj= DBHelper.ExecuteScalar(sql.ToString(),paramList.ToArray()) ; \r\n");
            sb.Append("    if(obj!=null){ return  " + returnStr + " ;}else{return -1;}\r\n");
            sb.Append("}\r\n");


            sb.Append("/// <summary>\r\n");
            sb.Append("/// 新增实例（事务）(返回ID)\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <param name=\"model\"></param>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static " + Convers.SqlType2CsharpType(sqldbtype) + " Create(" + tableName + " model,SqlCommand cmd)\r\n");
            sb.Append("{ \r\n");
            sb.Append(innerR.ToString());
            sb.Append("\r\n");
            sb.Append("     object obj= DBHelper.ExecuteScalar(sql.ToString(),paramList.ToArray(),cmd); \r\n");
            sb.Append("     if(obj!=null){ return  " + returnStr + " ;}else{return -1;}\r\n");
            sb.Append("}\r\n");
            #endregion
            return sb.ToString();
        }

        /// <summary>
        /// 一般和事务修改
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static string Update(DataTable dt, string tableName)
        {
            StringBuilder inner = new StringBuilder();
            inner.Append(" StringBuilder sql = new StringBuilder();\r\n");
            inner.Append("      sql.Append(\"UPDATE " + tableName + " \");\r\n");
            inner.Append("      sql.Append(\"SET  " + dt.Rows[1][2].ToString() + "=@" + dt.Rows[1][2].ToString() + "\");\r\n");
            for (int i = 2; i < dt.Rows.Count; i++)
            {
                inner.Append("      sql.Append(\"," + dt.Rows[i][2].ToString() + "=@" + dt.Rows[i][2].ToString() + "\");\r\n");
            }
            inner.Append("      sql.Append(\"  WHERE " + dt.Rows[0][2].ToString() + "=@" + dt.Rows[0][2].ToString() + "\");\r\n");
            inner.Append("\r\n");
            inner.Append("      List<SqlParameter> paramList = new List<SqlParameter>();\r\n");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Type type = Convers.SqlType2CsharpType(Convers.SqlTypeString2SqlType(dt.Rows[i][5].ToString()));
                if (type == typeof(DateTime))
                {
                    inner.Append("     paramList.Add(DBHelper.InitSqlParam(\"@" + dt.Rows[i][2].ToString() + "\",model." + dt.Rows[i][2].ToString() + "==DateTime.MinValue?new DateTime(1900,1,1):model." + dt.Rows[i][2].ToString() + "));\r\n");
                }
                else if (type == typeof(String))
                {
                    inner.Append("     paramList.Add(DBHelper.InitSqlParam(\"@" + dt.Rows[i][2].ToString() + "\",model." + dt.Rows[i][2].ToString() + "==null?\"\":model." + dt.Rows[i][2].ToString() + "));\r\n");
                }
                else
                {
                    inner.Append("     paramList.Add(DBHelper.InitSqlParam(\"@" + dt.Rows[i][2].ToString() + "\",model." + dt.Rows[i][2].ToString() + "));\r\n");
                }
            }
            inner.Append("\r\n");


            StringBuilder sb = new StringBuilder();
            sb.Append("/// <summary>\r\n");
            sb.Append("/// 更新实例\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <param name=\"model\"></param>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static bool Update(" + tableName + " model)\r\n");
            sb.Append("{ \r\n");
            sb.Append("     " + inner.ToString());
            sb.Append("     return (int)DBHelper.ExecuteNonQuery(sql.ToString(),paramList.ToArray())>0 ; \r\n");
            sb.Append("}\r\n");

            sb.Append("/// <summary>\r\n");
            sb.Append("/// 更新实例（事务）\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <param name=\"model\"></param>\r\n");
            sb.Append("/// <param name=\"cmd\"></param>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static bool Update(" + tableName + " model,SqlCommand cmd)\r\n");
            sb.Append("{ \r\n");
            sb.Append("     " + inner.ToString());
            sb.Append("\r\n");
            sb.Append("     return (int)DBHelper.ExecuteNonQuery(sql.ToString(),paramList.ToArray(),cmd)>0 ; \r\n");
            sb.Append("}\r\n");


            return sb.ToString();
        }

        /// <summary>
        /// 一般和事务删除
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static string Delete(DataTable dt, string tableName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("/// <summary>\r\n");
            sb.Append("/// 删除实例\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <param name=\"Id\">id必须是数字</param>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static bool DeleteById(Int64 Id)\r\n");
            sb.Append("{ \r\n");
            sb.Append("     string sql=\"DELETE FROM " + tableName + " WHERE " + dt.Rows[0][2].ToString() + "=@Id\"; \r\n");
            sb.Append("     SqlParameter paramList = DBHelper.InitSqlParam(\"@Id\",Id);\r\n");
            sb.Append("     return  (int)DBHelper.ExecuteNonQuery(sql,paramList)>0;\r\n");
            sb.Append("}\r\n");

            sb.Append("\r\n");

            sb.Append("/// <summary>\r\n");
            sb.Append("/// 删除实例（事务）\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <param name=\"Id\">id必须是数字</param>\r\n");
            sb.Append("/// <param name=\"cmd\"></param>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static bool DeleteById(Int64 Id,SqlCommand cmd)\r\n");
            sb.Append("{ \r\n");
            sb.Append("     string sql=\"DELETE FROM " + tableName + " WHERE " + dt.Rows[0][2].ToString() + "=@Id\"; \r\n");
            sb.Append("     SqlParameter paramList = DBHelper.InitSqlParam(\"@Id\",Id);\r\n");
            sb.Append("     return (int)DBHelper.ExecuteNonQuery(sql,paramList,cmd)>0;\r\n");
            sb.Append("}\r\n");

            return sb.ToString();
        }

        /// <summary>
        /// 一般和事务获取一个实例
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static string GetById(DataTable dt, string tableName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("/// <summary>\r\n");
            sb.Append("/// 获取实例\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <param name=\"ID\">ID必须是数字</param>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static " + tableName + " GetById(" + Convers.SqlType2CsharpType(Convers.SqlTypeString2SqlType(dt.Rows[0][5].ToString())) + "  ID)\r\n");
            sb.Append("{ \r\n");
            sb.Append("     string sql=\"SELECT * FROM " + tableName + " WHERE " + dt.Rows[0][2].ToString() + "=\"+ID; \r\n");
            sb.Append("     DataTable dt=DBHelper.ExecutQuery(sql.ToString()).Tables[0] ; \r\n");
            sb.Append("     if(dt==null||dt.Rows.Count<=0){return null;}\r\n");

            sb.Append("     return Convers<" + tableName + ">.T2L(dt)[0];\r\n");
            sb.Append("}\r\n");

            sb.Append("/// <summary>\r\n");
            sb.Append("/// 获取实例(事务)\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <param name=\"ID\">id必须是数字</param>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static " + tableName + " GetById(" + Convers.SqlType2CsharpType(Convers.SqlTypeString2SqlType(dt.Rows[0][5].ToString())) + " ID,SqlCommand cmd)\r\n");
            sb.Append("{ \r\n");
            sb.Append("     string sql=\"SELECT * FROM " + tableName + " WHERE " + dt.Rows[0][2].ToString() + "=\"+ID; \r\n");
            sb.Append("     \r\n");
            sb.Append("     DataTable dt= DBHelper.ExecutQuery(sql.ToString(),cmd).Tables[0] ; \r\n");
            sb.Append("     if(dt==null||dt.Rows.Count<=0){return null;}\r\n");
            sb.Append("     return Convers<" + tableName + ">.T2L(dt)[0];\r\n");
            sb.Append("}\r\n");
            return sb.ToString();
        }

        /// <summary>
        /// 获取一个集合
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static string GetList(DataTable dt, string tableName)
        {
            if (dt == null || dt.Rows.Count < 2)
            {
                return null;
            }

            StringBuilder paramStr = new StringBuilder();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Type type = Convers.SqlType2CsharpType(Convers.SqlTypeString2SqlType(dt.Rows[i][5].ToString()));
                SqlDbType sqldbtype = Convers.SqlTypeString2SqlType(dt.Rows[i][5].ToString());
                string defaultValue = Convers.GetTypeDefault(sqldbtype);

                if (type == typeof(String))
                {
                    paramStr.Append("   if(!string.IsNullOrWhiteSpace(model." + dt.Rows[i][2].ToString() + "))\r\n");
                    paramStr.Append("   {\r\n");
                    paramStr.Append("     sql+=\"  OR " + dt.Rows[i][2].ToString() + " LIKE '%'+@" + dt.Rows[i][2].ToString() + "+'%'\";\r\n");
                    paramStr.Append("     paramList.Add(DBHelper.InitSqlParam(\"@" + dt.Rows[i][2].ToString() + "\",model." + dt.Rows[i][2].ToString() + "));\r\n");
                    paramStr.Append("   }\r\n");
                }
                if (type == typeof(DateTime))
                {
                    paramStr.Append("   if(model." + dt.Rows[i][2].ToString() + "!=DateTime.MinValue && model." + dt.Rows[i][2].ToString() + "!=DateTime.MinValue)\r\n");
                    paramStr.Append("   {\r\n");
                    paramStr.Append("      sql+=\"  OR " + dt.Rows[i][2].ToString() + " LIKE '%'+@" + dt.Rows[i][2].ToString() + "+'%'\";\r\n");
                    paramStr.Append("      paramList.Add(DBHelper.InitSqlParam(\"@" + dt.Rows[i][2].ToString() + "\",model." + dt.Rows[i][2].ToString() + "));\r\n");
                    paramStr.Append("   }\r\n");
                }
                if (type == typeof(Int64))
                {
                    paramStr.Append("   if(model." + dt.Rows[i][2].ToString() + "!=Int64.MinValue && model." + dt.Rows[i][2].ToString() + "!=Int64.MinValue)\r\n");
                    paramStr.Append("   {\r\n");
                    paramStr.Append("      sql+=\"  OR " + dt.Rows[i][2].ToString() + " = @" + dt.Rows[i][2].ToString() + "\";\r\n");
                    paramStr.Append("      paramList.Add(DBHelper.InitSqlParam(\"@" + dt.Rows[i][2].ToString() + "\",model." + dt.Rows[i][2].ToString() + "));\r\n");
                    paramStr.Append("   }\r\n");
                }
                if (type == typeof(Int32))
                {
                    paramStr.Append("   if(model." + dt.Rows[i][2].ToString() + "!=Int32.MinValue && model." + dt.Rows[i][2].ToString() + "!=Int32.MinValue)\r\n");
                    paramStr.Append("   {\r\n");
                    paramStr.Append("      sql+=\"  OR " + dt.Rows[i][2].ToString() + " = @" + dt.Rows[i][2].ToString() + "\";\r\n");
                    paramStr.Append("      paramList.Add(DBHelper.InitSqlParam(\"@" + dt.Rows[i][2].ToString() + "\",model." + dt.Rows[i][2].ToString() + "));\r\n");
                    paramStr.Append("   }\r\n");
                }
            }

            StringBuilder sb = new StringBuilder();
            sb.Append("/// <summary>\r\n");
            sb.Append("/// 获取实例对象\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <param name=\"model\">实例对象中的参数,或关系</param>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static List<" + tableName + "> GetList(" + tableName + " model,int pageIndex,int pageSize,out int total)\r\n");
            sb.Append("{ \r\n");
            sb.Append("   string sql=\"SELECT * FROM " + tableName + " WHERE 1=1\";\r\n");
            sb.Append("   List<SqlParameter> paramList = new List<SqlParameter>();\r\n");
            sb.Append(paramStr.ToString());
            sb.Append("   \r\n");
            sb.Append("   string OrderByStr = \" ORDER BY " + dt.Rows[0][2].ToString() + " DESC\";\r\n");
            sb.Append("   \r\n");
            sb.Append("   DataTable dt= DBHelper.QueryPage(sql.ToString(),OrderByStr,paramList.ToArray(),pageIndex,pageSize,out total) ; \r\n");
            sb.Append("   return Convers<" + tableName + ">.T2L(dt);\r\n");
            sb.Append("}\r\n");

            return sb.ToString();
        }

        /// <summary>
        /// 获取实体列表，不分页
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static string GetList(string tableName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("/// <summary>\r\n");
            sb.Append("/// 获取实体列表，不分页\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <param name=\"dic\">查询字段，值，And关系</param>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static List<" + tableName + "> GetList(Dictionary<string,object> dic)\r\n");
            sb.Append("{ \r\n");
            sb.Append("   string sql=\"SELECT * FROM " + tableName + " WHERE 1=1\";\r\n");
            sb.Append("   List<SqlParameter> paramList = new List<SqlParameter>();\r\n");
            sb.Append("   foreach (var item in dic) \r\n");
            sb.Append("   { \r\n");
            sb.Append("       sql += \" AND \" + item.Key + \"=@\" + item.Key; \r\n");
            sb.Append("       paramList.Add(DBHelper.InitSqlParam(\"@\" + item.Key, item.Value)); \r\n");
            sb.Append("   } \r\n");
            sb.Append("   DataTable dt= DBHelper.ExecutQuery(sql.ToString(),paramList.ToArray()).Tables[0] ;  \r\n");
            sb.Append("   return Convers<" + tableName + ">.T2L(dt);;\r\n");
            sb.Append("}\r\n");

            return sb.ToString();
        }


        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static string GetQuery(string tableName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("/// <summary>\r\n");
            sb.Append("/// 获取实体列表，分页\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <param name=\"dic\">查询字段，值，或关系</param>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static List<" + tableName + "> GetQueryList(Dictionary<string,object> dic,string sortColumn,int pageIndex,int pageSize,out int total)\r\n");
            sb.Append("{ \r\n");
            sb.Append("   string sql=\"SELECT * FROM " + tableName + " WHERE 1=1\";\r\n");
            sb.Append("   List<SqlParameter> paramList = new List<SqlParameter>();\r\n");

            sb.Append("   if(dic!=null&&dic.Count>0){ \r\n");
            sb.Append("   int i=0; \r\n");
            sb.Append("   sql+=\" And (\"; \r\n");
            sb.Append("   foreach (var item in dic) \r\n");
            sb.Append("   { \r\n");
            sb.Append("      if(i==0){ \r\n");
            sb.Append("           sql += item.Key + \" LIKE '%'+ @\" + item.Key+\" + '%'\";} \r\n");
            sb.Append("      else{  sql += \" Or \" + item.Key + \" LIKE '%'+ @\" + item.Key+\" + '%'\";} \r\n");
            sb.Append("      paramList.Add(DBHelper.InitSqlParam(\"@\" + item.Key, item.Value)); \r\n");
            sb.Append("      i+=1;\r\n");
            sb.Append("   } \r\n");
            sb.Append("   sql+=\")\"; \r\n");
            sb.Append("   } \r\n");
            sb.Append("  \r\n");


            sb.Append("   List<SortParam> sorts=new List<SortParam>();  \r\n");
            sb.Append("   sorts.Add(new SortParam() { SortColunm = sortColumn , SortType = SortType.DESC});  \r\n");
            sb.Append("  \r\n");
            //sb.Append("   DataTable dt= DBHelper.ExecutQuery(sql.ToString(),paramList.ToArray()).Tables[0] ;  \r\n");
            sb.Append("   DataTable dt= DBHelper.QueryPage(sql.ToString(),sorts,paramList.ToArray(),pageIndex,pageSize,out total); \r\n");
            sb.Append("   return Convers<" + tableName + ">.T2L(dt); \r\n");
            sb.Append("}\r\n");


            sb.Append("/// <summary>\r\n");
            sb.Append("/// 获取实体列表，分页\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <param name=\"dic\">查询字段，值，Dic1或关系,Dic2与关系</param>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static List<" + tableName + "> GetQueryList(Dictionary<string,object> dic1,Dictionary<string,object> dic2,string sortColumn,int pageIndex,int pageSize,out int total)\r\n");
            sb.Append("{ \r\n");
            sb.Append("   string sql=\"SELECT * FROM " + tableName + " WHERE 1=1\";\r\n");
            sb.Append("   List<SqlParameter> paramList = new List<SqlParameter>();\r\n");

            //或关系
            sb.Append("   if(dic1!=null&&dic1.Count>0){ \r\n");
            sb.Append("   int i=0; \r\n");
            sb.Append("   sql+=\" And (\"; \r\n");
            sb.Append("   foreach (var item in dic1) \r\n");
            sb.Append("   { \r\n");
            sb.Append("      if(i==0){ \r\n");
            sb.Append("           sql += item.Key + \" LIKE '%'+ @\" + item.Key+\" + '%'\";} \r\n");
            sb.Append("      else{  sql += \" Or \" + item.Key + \" LIKE '%'+ @\" + item.Key+\" + '%'\";} \r\n");
            sb.Append("      paramList.Add(DBHelper.InitSqlParam(\"@\" + item.Key, item.Value)); \r\n");
            sb.Append("      i+=1;\r\n");
            sb.Append("   } \r\n");
            sb.Append("   sql+=\")\"; \r\n");
            sb.Append("   } \r\n");
            sb.Append("  \r\n");

            //且关系
            sb.Append("   if(dic2!=null&&dic2.Count>0){ \r\n");
            sb.Append("   foreach (var item in dic2) \r\n");
            sb.Append("   { \r\n");
            sb.Append("       sql += \" And \" + item.Key + \"=@\" + item.Key; \r\n");
            sb.Append("       paramList.Add(DBHelper.InitSqlParam(\"@\" + item.Key, item.Value)); \r\n");
            sb.Append("   } \r\n");
            sb.Append("   } \r\n");
            sb.Append("  \r\n");

            sb.Append("  \r\n");
            sb.Append("   List<SortParam> sorts=new List<SortParam>();  \r\n");
            sb.Append("   sorts.Add(new SortParam() { SortColunm = sortColumn , SortType = SortType.DESC});  \r\n");
            sb.Append("  \r\n");

            sb.Append("   DataTable dt= DBHelper.QueryPage(sql.ToString(),sorts,paramList.ToArray(),pageIndex,pageSize,out total); \r\n");
            sb.Append("   return Convers<" + tableName + ">.T2L(dt); \r\n");
            sb.Append("}\r\n");

            return sb.ToString();
        }

        private static string GetSelectQuery(string tableName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("/// <summary>\r\n");
            sb.Append("/// 获取DataTable分页\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <param name=\"dic\">查询字段，值，Dic1或关系,Dic2与关系</param>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static DataTable GetSelects(string columns,Dictionary<string,object> dic1,Dictionary<string,object> dic2,string sortColumn,int pageIndex,int pageSize,out int total)\r\n");
            sb.Append("{ \r\n");
            sb.Append("   string sql=\"SELECT \"+columns+\" FROM " + tableName + " WHERE 1=1\";\r\n");
            sb.Append("   List<SqlParameter> paramList = new List<SqlParameter>();\r\n");

            //或关系
            sb.Append("   if(dic1!=null&&dic1.Count>0){ \r\n");
            sb.Append("   int i=0; \r\n");
            sb.Append("   sql+=\" And (\"; \r\n");
            sb.Append("   foreach (var item in dic1) \r\n");
            sb.Append("   { \r\n");
            sb.Append("      if(i==0){ \r\n");
            sb.Append("           sql += item.Key + \" LIKE '%'+ @\" + item.Key+\" + '%'\";} \r\n");
            sb.Append("      else{  sql += \" Or \" + item.Key + \" LIKE '%'+ @\" + item.Key+\" + '%'\";} \r\n");
            sb.Append("      paramList.Add(DBHelper.InitSqlParam(\"@\" + item.Key, item.Value)); \r\n");
            sb.Append("      i+=1;\r\n");
            sb.Append("   } \r\n");
            sb.Append("   sql+=\")\"; \r\n");
            sb.Append("   } \r\n");
            sb.Append("  \r\n");

            //且关系
            sb.Append("   if(dic2!=null&&dic2.Count>0){ \r\n");
            sb.Append("   foreach (var item in dic2) \r\n");
            sb.Append("   { \r\n");
            sb.Append("       sql += \" And \" + item.Key + \"=@\" + item.Key; \r\n");
            sb.Append("       paramList.Add(DBHelper.InitSqlParam(\"@\" + item.Key, item.Value)); \r\n");
            sb.Append("   } \r\n");
            sb.Append("   } \r\n");
            sb.Append("  \r\n");

            sb.Append("  \r\n");
            sb.Append("   List<SortParam> sorts=new List<SortParam>();  \r\n");
            sb.Append("   sorts.Add(new SortParam() { SortColunm = sortColumn , SortType = SortType.DESC});  \r\n");
            sb.Append("  \r\n");

            sb.Append("   DataTable dt= DBHelper.QueryPage(sql.ToString(),sorts,paramList.ToArray(),pageIndex,pageSize,out total); \r\n");
            sb.Append("   return dt; \r\n");
            sb.Append("}\r\n");

            return sb.ToString();
        }


        /// <summary>
        /// 获取所有实例
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static string GetAllModels(DataTable dt, string tableName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("/// <summary>\r\n");
            sb.Append("/// 获取所有实例对象\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static List<" + tableName + "> GetAllModels()\r\n");
            sb.Append("{ \r\n");
            sb.Append("   string sql=\"SELECT * FROM " + tableName + "\";\r\n");
            sb.Append("   DataTable dt= DBHelper.ExecutQuery(sql).Tables[0] ; \r\n");
            sb.Append("   return Convers<" + tableName + ">.T2L(dt);\r\n");
            sb.Append("}\r\n");

            return sb.ToString();
        }

        private static string GetTopBy(string tableName)
        {

            StringBuilder sb = new StringBuilder();
            sb.Append("/// <summary>\r\n");
            sb.Append("/// 获取所有实例对象\r\n");
            sb.Append("/// </summary>\r\n");
            sb.Append("/// <returns></returns>\r\n");
            sb.Append("public static List<" + tableName + "> GetTopBy(int top,string orderby)\r\n");
            sb.Append("{ \r\n");
            sb.Append("   string sql=\"SELECT top( \"+top+\") * FROM " + tableName + " order by \"+orderby +\" desc \";\r\n");
            sb.Append("   DataTable dt= DBHelper.ExecutQuery(sql).Tables[0] ; \r\n");
            sb.Append("   return Convers<" + tableName + ">.T2L(dt);\r\n");
            sb.Append("}\r\n");

            return sb.ToString();
        }

        /// <summary>
        /// 是否存在数据
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private static string IsExist(string tableName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" /// <summary> \r\n");
            sb.Append(" /// 判断数据是否存在 \r\n");
            sb.Append(" ///<param name=\"dic\">key：字段名称、value字段值</param> \r\n");
            sb.Append(" /// <returns>true为存在</returns> \r\n");
            sb.Append(" public static bool IsExist(Dictionary<string,object> dic) \r\n");
            sb.Append(" { \r\n");
            sb.Append("     string sql = \"SELECT top 1 1 FROM " + tableName + " WHERE 1=1\"; \r\n");
            sb.Append("     List<SqlParameter> paramList = new List<SqlParameter>(); \r\n");
            sb.Append("     if (dic == null || dic.Count <= 0) \r\n");
            sb.Append("     { \r\n");
            sb.Append("         return true; \r\n");
            sb.Append("     } \r\n");
            sb.Append("     foreach (var item in dic) \r\n");
            sb.Append("     { \r\n");
            sb.Append("         sql += \" AND \" + item.Key + \"=@\" + item.Key; \r\n");
            sb.Append("         paramList.Add(DBHelper.InitSqlParam(\"@\" + item.Key, item.Value)); \r\n");
            sb.Append("     } \r\n");
            sb.Append("     int num= (int)DBHelper.ExecuteScalar(sql,paramList.ToArray()); \r\n");
            sb.Append("     return num > 0; \r\n");
            sb.Append(" } \r\n");

            return sb.ToString();
        }

        #endregion

        #region 写入文件


        /// <summary>
        /// 保存model
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        /// <summary>
        /// 保存model
        /// </summary>
        /// <param name="path"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        private static bool WriteToModels(string tableName, string code)
        {
            string modeDir = modelPath;

            if (!Directory.Exists(modeDir))
            {
                Directory.CreateDirectory(modeDir);
            }

            string filePath = modeDir + "/" + tableName + ".cs";

            if (File.Exists(filePath))
            {
                File.Delete(filePath);

            }

            using (StreamWriter sw = File.AppendText(filePath)) //写
            {
                sw.WriteLine(code);
            }

            WriteToProj("Model\\" + tableName + ".cs");

            return true;
        }

        /// <summary>
        /// 保存dal基础方法
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="code"></param>
        /// <returns></returns>
        private static bool WriteToDals(string tableName, string code)
        {
            string baseDalDir = baseDalPath;

            if (!Directory.Exists(baseDalDir))
            {
                Directory.CreateDirectory(baseDalDir);
            }

            string filePath = baseDalDir + "/" + tableName + "BaseDAL.cs";

            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }


            using (StreamWriter sw = File.AppendText(filePath)) //写
            {
                sw.WriteLine(code);
            }

            WriteToProj("BaseDAL\\" + tableName + "BaseDAL.cs");

            return true;
        }

        /// <summary>
        /// 给basecode项目文件写东西
        /// </summary>
        /// <param name="path">cs地址</param>
        private static void WriteToProj(string csPath)
        {
            string data = "";

            using (StreamReader sr = File.OpenText(projPath))
            {
                data = sr.ReadToEnd();
            }

            XmlDocument xml = new XmlDocument();

            xml.LoadXml(data);

            if (xml.HasChildNodes)
            {
                XmlNode root = xml.LastChild;
                foreach (XmlNode node in root)
                {
                    if (node.Name == null || node.FirstChild == null)
                    {
                        continue;
                    }
                    if (node.Name == "ItemGroup" && node.FirstChild.Name == "Compile")
                    {
                        XmlNode n = node.FirstChild.CloneNode(true);
                        n.Attributes["Include"].Value = csPath;

                        foreach (XmlNode eq in node.ChildNodes)
                        {
                            if (eq.Name == n.Name
                                && eq.Attributes["Include"].Value == n.Attributes["Include"].Value)
                            {
                                return;
                            }
                        }

                        node.AppendChild(n);
                    }
                }

            }
            xml.Save(projPath);
        }

        #endregion
    }
}
